import pymysql

# 以下为必填参数
connection = pymysql.connect(user='ASchool_user',  # 用户名
                             password='9L647LCT1tUlu2e3',  # 密码
                             host='218.22.21.242',  # 服务器地址
                             port=21541,  # 数据库端口
                             database='ASchoolDB'  # 要连接的数据库
                             )

try:
    with connection.cursor() as cursor:
        # 确保目标表存在
        # cursor.execute("CREATE TABLE IF NOT EXISTS tmp_navigation_detail LIKE tmp_navigation_detail")
        # connection.commit()

        # 初始化计数器
        count = 0

        # 循环直到没有更多数据
        while True:
            # 构建LIMIT语句
            cursor.execute("""
                INSERT INTO tmp_navigation_detail ( title, content, description, publisher, publish_date, read_total, enable, del_status, navigation_id ) SELECT
                        new_posts.Title AS title,
                        new_posts.Content AS content,
                        new_posts.Summary AS description,
                        new_posts.PublishUserId AS publisher,
                        new_posts.DatePublished AS publish_date,
                        new_posts.ViewCount AS read_total,
                        new_posts.IsPublished AS enable,
                        new_posts.IsDeleted AS del_status,
                        gcms_navigation.navigation_id AS navigation_id 
                        FROM
                            new_posts
                            JOIN gcms_navigation ON new_posts.Id = gcms_navigation.gcms_id 
                        WHERE
                            new_posts.IsPublished = 1 
                            AND new_posts.IsDeleted = 0
                LIMIT %s, 1000
            """, (count,))
            connection.commit()

            # 检查是否有数据被插入
            cursor.execute("""
                SELECT FOUND_ROWS()
            """)
            rows_affected = cursor.fetchone()[0]

            # 如果没有数据被插入，则退出循环
            if rows_affected == 0:
                break

            # 更新计数器
            count += 1000

finally:
    connection.close()
